import pandas as pds
import matplotlib.pylab as plb
import wordcloud as wc
import pymysql
import numpy

'''
从数据库获取待分析的数据,数据来自老师的数据,自己爬取的数据太少
'''


def get_data_by_mysql():
    host = ""
    user = ""
    passwd = ""
    port = 3306
    db = ""
    charset = "utf8"
    try:
        conn = pymysql.connect(host, user, passwd, db, port, charset)
        sql_str = "SELECT id,name,hits,comment FROM myhexun"
        data_frame = pds.read_sql(sql_str, conn)
        return data_frame
    except Exception as err:
        print(err)
    finally:
        conn.close()


'''
获取词云图
'''


def get_cloud_image():
    hx_data = get_data_by_mysql()
    trans_data = hx_data.T
    names = trans_data.values[1]
    tmp = ""
    for n in names:
        tmp += str(n) + " "
    font = r"G:\DOWNLOAD\msyh.ttf"
    image = plb.imread("dx.jpg")
    # collocations : bool, default=True #是否包括两个词的搭配
    wc_data = wc.WordCloud(collocations=False, font_path=font, mask=image, background_color="white").generate(tmp)
    plb.imshow(wc_data)
    plb.show()


'''
不做预处理直接获取坐标图
'''


def get_axis_image():
    hx_data = get_data_by_mysql()
    print(hx_data.describe())
    trans_data = hx_data.T
    x = trans_data.values[3]  # 评论数
    y = trans_data.values[2]  # 点击量
    plb.title("hexun.com")
    plb.xlabel("comment")
    plb.ylabel("hits")
    plb.plot(x, y, "or", label="comment-hits")
    plb.legend()
    plb.show()


'''
预处理数据后获取坐标图
'''


def get_axis_image_by_prep(data_t):
    x = data_t[3]  # 评论数
    y = data_t[2]  # 点击量
    plb.title("hexun.com")
    plb.xlabel("comment")
    plb.ylabel("hits")
    plb.plot(x, y, "or", label="comment-hits")
    plb.legend()
    plb.show()


'''
获取坐标图,有子图
'''


def get_axis_image_sub():
    hx_data = get_data_by_mysql()
    trans_data = hx_data.T
    x = trans_data.values[3]  # 评论数
    y = trans_data.values[2]  # 点击量

    data1 = get_data_by_modify(hx_data)
    data2 = get_data_by_delete(hx_data)

    x1 = data1[3]  # 评论数
    y1 = data1[2]  # 点击量

    x2 = data2[3]  # 评论数
    y2 = data2[2]  # 点击量

    plb.subplot(2, 2, 1)
    plb.title("modify")
    plb.plot(x1, y1, "or")
    plb.subplot(2, 2, 2)
    plb.title("delete")
    plb.plot(x2, y2, "or")
    plb.subplot(2, 1, 2)
    plb.title("hexun.com")
    plb.plot(x, y, "or")
    plb.show()


'''
改值预处理
'''


def get_data_by_modify(data_frame):
    row_count = len(data_frame.values)
    col_count = len(data_frame.values[0])
    tmp_data = data_frame.values
    for row in range(0, row_count):
        for col in range(0, col_count):
            if col == 3 and tmp_data[row][3] > 30:
                tmp_data[row][col] = 5
            elif col == 2 and tmp_data[row][2] > 6000:
                tmp_data[row][col] = 700
    data = tmp_data.T
    return data


'''
删除预处理
'''


def get_data_by_delete(data_frame):
    row_count = len(data_frame.values)
    col_count = len(data_frame.values[0])
    tmp_data = data_frame.values
    x = 0
    for row in range(0, row_count):
        for col in range(0, col_count):
            if tmp_data[row][3] > 30:
                continue
            elif tmp_data[row][2] > 6000:
                continue
            else:
                if x == 0:
                    new_data = tmp_data[row]
                else:
                    new_data = numpy.row_stack((new_data, tmp_data[row]))
                x += 1
    data = new_data.T
    return data


'''
预处理数据后获得对应列数据的直方图
'''


def get_hist_image(data, col):
    if col == "comment":
        c_max = data[3].max()
        c_min = data[3].min()
        c_range = c_max - c_min
        c_width = c_range/10
        c_data = numpy.arange(c_min, c_max, c_width)
        plb.hist(data[3], c_data)
        plb.show()
    elif col == "hits":
        h_max = data[2].max()
        h_min = data[2].min()
        h_range = h_max - h_min
        h_width = h_range / 10
        h_data = numpy.arange(h_min, h_max, h_width)
        plb.hist(data[2], h_data)
        plb.show()
    else:
        pass















